#!/usr/bin/env python
# -*- coding: utf-8 -*-

#http://mysql-python.sourceforge.net/MySQLdb.html
#http://codex.gallery2.org/Database_Schema_%28G2%29

import MySQLdb
from xml.etree import ElementTree


class G2Object():
    def __init__(self,id):
        self.id = id
        self.pathComponent = None
        self.title = None
        self.description = None
        self.summary = None
        
class Photo(G2Object):
    def __init__(self,id):
        G2Object.__init__(self, id)

class Album(G2Object):
    def __init__(self,id):
        G2Object.__init__(self, id)
        self.childAlbums = []
        self.parent = None
        self.photos = []
    
    def getPath(self):
        path = []
        tmp = self.parent
        while tmp and tmp.pathComponent:
            path.append(tmp.pathComponent)
            tmp = tmp.parent
        path.reverse()
        if self.pathComponent:
            path.append(self.pathComponent)

        return '/'.join(path) 
            
    def addChildAlbum(self,album):
        self.childAlbums.append(album)
        
    def addPhotos(self,photos):
        self.photos = photos
        
def output(album,albumsElem):
    albumElem = ElementTree.SubElement(albumsElem, "album")
    albumElem.set("path", album.getPath())
    titleElem = ElementTree.SubElement(albumElem, "title")
    if album.title:
        titleElem.text = album.title
    descriptionElem = ElementTree.SubElement(albumElem, "description")
    if album.description:
        descriptionElem.text = album.description
    summaryElem = ElementTree.SubElement(albumElem, "summary")
    if album.summary:
        summaryElem.text = album.summary   
    
    photosElem = ElementTree.SubElement(albumElem, "photos")
    
    for photo in album.photos:
        photoElem = ElementTree.SubElement(photosElem, "photo")
        photoElem.set("path", album.getPath() + "/" + photo.pathComponent)
        titleElem = ElementTree.SubElement(photoElem, "title")
        if photo.title:
            titleElem.text = photo.title
        descriptionElem = ElementTree.SubElement(photoElem, "description")
        if photo.description:
            descriptionElem.text = photo.description
        summaryElem = ElementTree.SubElement(photoElem, "summary")
        if photo.summary:
            summaryElem.text = photo.summary   
  
    for child in album.childAlbums:
        output(child,albumsElem)


def populate(album,cursor):
    cursor.execute("SELECT I.g_description, I.g_summary, I.g_title, F.g_pathComponent FROM g2_AlbumItem A, g2_Item I, g2_FileSystemEntity F WHERE A.g_id = I.g_id AND A.g_id = F.g_id AND F.g_id = %s" % album.id);
    for res in cursor.fetchall():
        album.description = res[0]
        album.summary = res[1]
        album.title = res[2]
        album.pathComponent = res[3]
    
    photos = []    
    cursor.execute("SELECT I.g_id, I.g_title, F.g_pathComponent, I.g_description, I.g_summary FROM g2_Item I, g2_FileSystemEntity F, g2_ChildEntity C WHERE I.g_id = C.g_id AND I.g_id = F.g_id AND I.g_canContainChildren = 0 AND C.g_parentId = %s" % album.id)
    for res in cursor.fetchall():
        photo = Photo(res[0])
        photo.title = res[1]
        photo.pathComponent = res[2] 
        photo.description = res[3]
        photo.summary = res[4]
        photos.append(photo)
    
    album.photos = photos
    for child in album.childAlbums:
        populate(child,cursor)

albums = {}

conn = None
cursor = None
try:
    conn = MySQLdb.connect(host="<YOUR MYSQL HOST>", user="<YOUR DATABASE USER NAME>", passwd="<YOUR DATABSE PASSWORD>", db="<THE MYSQL DATABASE NAME>",use_unicode=True);
    cursor = conn.cursor();

    cursor.execute("SELECT g_id FROM g2_AlbumItem")
    albumIDS = set()
    for res in cursor.fetchall():
        albumIDS.add(res[0])
    
    for albumID in albumIDS:
        cursor.execute("SELECT  g_parentId FROM g2_ChildEntity WHERE g_id = %s"  % albumID)
        if albums.has_key(albumID):
            child = albums[albumID]
        else:
            child = Album(albumID)
            albums[albumID] = child
    
        for res in cursor.fetchall():
            if albums.has_key(res[0]):
                parent = albums[res[0]]
            else:
                parent = Album(res[0])
                albums[res[0]] = parent
            parent.addChildAlbum(child)
            child.parent = parent
    
    rootAlbum = albums[7L] # By definition the root album has ID 7
    populate(rootAlbum,cursor)

finally: 
    if cursor:
        cursor.close()
    if conn:
        conn.close()

root = ElementTree.Element("albums")
output(rootAlbum,root)
tree = ElementTree.ElementTree(root)
tree.write("albums.xml")


